package zhtt.demo02;

import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.junit.Test;
import zhtt.JunitTestUtil;
import zhtt.demo01.Student;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Created by zhtt on 2017/4/16.
 */
public class JunitTest02 extends JunitTestUtil {

    @Test
    /**
     * 关联查询
     */
    public void test14(){
         List<Country> countryList = session.createCriteria(Country.class)
         .createAlias("ministerSet","minister")
//         .createAlias("mate","mt")
         .add(Restrictions.gt("minister.id",1))
         .list();
        System.out.println(countryList);
    }

    @Test
    /**
     * 关联查询
     */
    public void test15(){
        /**
         (createAlias()并不创建一个新的Criteria实例)Cat实例所保存的之前两次查询所返回的kittens集合是没有被条件预过滤的.
         如果你希望只获得符合条件的kittens,你必须使用returnMaps().
         */
        List cats = session.createCriteria(Country.class)
                .createCriteria("ministerSet","minister")
                .add(Restrictions.like("name","%0.%"))
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        Iterator iter = cats.iterator();
        System.out.println("----------------------------------------");
        System.out.println("----------------------------------------");
        while(iter.hasNext()) {
            Map map = (Map) iter.next();
            Country country = (Country) map.get(Criteria.ROOT_ALIAS);
            Minister ministerList = (Minister) map.get("minister");
            System.out.println();
            System.out.println("country:"+country);
            System.out.println("ministerList:"+ministerList);
        }
    }

    @Test
    /**
     * 动态关联抓取
     * 这个查询可以通过外连接抓取ministerSet
     * from country left join minister
     */
    public void test16(){
        List<Country> countryList = session.createCriteria(Country.class)
                .add(Restrictions.like("name","%0.3%"))
                .setFetchMode("ministerSet",FetchMode.EAGER)
                .list();
        System.out.println(countryList);
    }

    @Test
    /**
     * 动态关联抓取
     * 这个查询可以通过外连接抓取country
     * from minister left join country
     */
    public void test17(){
        List<Minister> ministerList = session.createCriteria(Minister.class)
                .add(Restrictions.like("name","%0.9%"))
                .setFetchMode("country",FetchMode.EAGER)
                .list();
        System.out.println(ministerList);
    }

    @Test
    public void test18(){
        Country country=new Country("%0.4%");
        Example example = Example.create(country)
                .excludeZeroes() //不含零值性质
                .excludeProperty("0") //排除名为“0”的属性
                .ignoreCase() //执行不区分大小写的字符串比较
                .enableLike(); //使用字符串比较
        List results = session.createCriteria(Country.class)
                .add(example)
                .list();
        System.out.println(results);
        /**
         * 甚至可以使用examples在关联对象上放置条件.
         List results = session.createCriteria(Cat.class)
         .add(Example.create(cat))
         .createCriteria("mate")
         .add(Example.create(cat.getMate()))
         .list();
         */
    }

    @Test
    /**
     * 投影：返回总数
     * 执行SQL：select count(*) as y0_ from zhtt07_country this_ where this_.name like ?
     * 打印结果：[1]
     */
    public void test19(){
        List results = session.createCriteria(Country.class)
                .setProjection(Projections.rowCount())
                .add(Restrictions.like("name","%0.9%"))
                .list();
        System.out.println(results);
    }

    @Test
    public void test20(){
        List studentList= session.createSQLQuery("select id,age from zhtt11_student").setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        System.out.println(studentList);

        List results = session.createCriteria(Student.class)
                .setProjection(Projections.projectionList()
                        .add(Projections.rowCount())
                        .add(Projections.avg("id"))
                        .add(Projections.max("id"))
                        .add(Projections.groupProperty("age"))
                )
                .list();
        System.out.println(studentList);
        for(int i=0;i<results.size();i++){
            Object[] res=(Object[])results.get(i);
            System.out.print("\t 总条数："+((Long)res[0]).intValue());
            System.out.print("\t 平均值："+((Double)res[1]).intValue());
            System.out.print("\t 最大值："+((Integer)res[2]).intValue());
            System.out.println("\t age："+((Integer)res[3]).intValue());
        }
    }
}
/**
 Example exampleUser = Example.create(u)
 .ignoreCase() // 忽略大小写
 .enableLike(MatchMode.ANYWHERE); // 对String类型的属性,无论在那里值在那里都匹配.相当于%value%
 **/